package util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 操作数据库的接口，都是用存储过程，暂时完成了一部分
 * @author wj
 *
 */
public class Java_JDBC {
	private static final String BDURL = "jdbc:sqlserver://localhost:1433; DatabaseName=sunvote";
	private static final String USERNAME = "sa";
	private static final String PASSWORD = "123456";
	private static final String DRIVENAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	Connection connection = null;

	public Java_JDBC() throws Exception{
		Class.forName(DRIVENAME);
		connection = DriverManager.getConnection(BDURL, USERNAME, PASSWORD);
		System.out.println("数据库连接成功");
	}
	
//	public void saveAsDatabase(String wordFilePath, String RecordID) {
//		Connection conn = null; 
//		PreparedStatement pstmt = null;
//		PoolingDataSource pool = null;
//		File file = null;
//		String sql = "";
//		try {
//			sql = " UPDATE Document_File SET FileBody = ? WHERE RecordID = ? ";
//			pool = new PoolingDataSource();
//			conn = pool.getConnection();
//			file = new File(wordFilePath);
//			InputStream is = new FileInputStream(file);
//			byte[] blobByte =new byte[is.available()];
//			is.read(blobByte);
//			is.close();
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setBinaryStream(1,(new ByteArrayInputStream(blobByte)), blobByte.length);
//			pstmt.setString(2, RecordID);
//			pstmt.executeUpdate();
//			
//		}catch(Exception e) {
//			
//		}finally {
//			pool.closePrepStmt(pstmt);
//			pool.closeConnection(conn);
//		}
//	}
	
	/**
	 * 学生登陆
	 * @param userNumber
	 * @param passworld
	 * @return
	 */
	public int studentLogin(String userNumber, String passworld, String className) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call studentLogin(?,?,?,?) }");
			statement.setString(1, userNumber);
			statement.setString(2, passworld);
			statement.setString(3, className);
			statement.registerOutParameter(4, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(4);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	/**
	 * 查询班级名称
	 * @return
	 */
	public List<String> searchClassName() {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call searchClassName() }");
			ResultSet set = statement.executeQuery();
			List<String> dataList = new ArrayList<String>();
			while (set.next()) {
				String paperName = set.getString(1);
				dataList.add(paperName);
			}
			return dataList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	
	}
	
	
	/**
	 * 查询试卷名称
	 * @return
	 */
	public List<String> searchPaperName() {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call searchPaperName() }");
			ResultSet set = statement.executeQuery();
			List<String> dataList = new ArrayList<String>();
			while (set.next()) {
				String paperName = set.getString(1);
				dataList.add(paperName);
			}
			return dataList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	
	}
	
	/**
	 * 查询学生总人数
	 * @return
	 */
	public int searchStudentCount(String className) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call searchStudentCount(?,?) }");
			statement.setString(1, className);
			statement.registerOutParameter(2, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(2);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	/**
	 * 查询签到人数
	 * @return
	 */
	public int searchSignCount(String className) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call searchSignCount(?,?) }");
			statement.setString(1, className);
			statement.registerOutParameter(2, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(2);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	/**
	 * 清空签到表
	 * @return
	 */
	public int clearSignedTable() {
		try {
			System.out.println("清空了签到表");
			CallableStatement statement = connection
					.prepareCall("{ call clearSignedTable(?) }");
			statement.registerOutParameter(1, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	/**
	 * 清空答题表
	 * @return
	 */
	public int clearAnswerTable() {
		try {
			System.out.println("清空了答题表");
			CallableStatement statement = connection
					.prepareCall("{ call clearAnswerTable(?) }");
			statement.registerOutParameter(1, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	/**
	 * 学生签到
	 * @param studentId
	 * @param signTime
	 * @return
	 */
	public int studentSign(String studentId,String signTime,String className) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call studentSigne(?,?,?,?) }");
			statement.setString(1, studentId);
			statement.setString(2, signTime);
			statement.setString(3, className);
			statement.registerOutParameter(4, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(4);
		} catch (SQLException e) {
			e.printStackTrace();
			return 20;
		}
	}
	
	/**
	 * 上传试卷到数据库
	 * @param studentId
	 * @param signTime
	 * @return
	 */
	public int addPaper(String paperName, String paperPath) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call addPaper(?,?,?) }");
			statement.setString(1, paperName);
			statement.setString(2, paperPath);
			statement.registerOutParameter(3, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(3);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * 从数据库下载试卷
	 */
	public String getPaperPath(String paperName) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call getPaperPath(?,?) }");
			statement.setString(1, paperName);
			statement.registerOutParameter(2, java.sql.Types.VARCHAR);
			statement.execute();
			return statement.getString(2);
		} catch (SQLException e) {
			e.printStackTrace();
			return "";
		}
	}
	

	/**
	 * 学生答题
	 * 
	 * @param studentId
	 *            学生编号
	 * @param exercisesId
	 *            题目编号
	 * @param answer
	 *            答案
	 * @return 答题状态
	 */
	public int answerQuestion(String exercisesId, String studentId, String answer, String signedClass, String paperName) {
		try {
			System.out.println("exercisesId:  "+exercisesId+"    "+studentId+"    "+answer);
			CallableStatement statement3 = connection
					.prepareCall("{ call answer(?,?,?,?,?,?) }");
			statement3.setInt(1, Integer.parseInt(exercisesId));
			statement3.setString(2, studentId);
			statement3.setString(3, answer);
			statement3.setString(4, signedClass);
			statement3.setString(5, paperName);
			statement3.registerOutParameter(6, java.sql.Types.INTEGER);
			statement3.execute();
			return statement3.getInt(6);
		} catch (SQLException e) {
			e.printStackTrace();
			return 30;
		}
	}

	/**
	 * 老师登陆
	 * @param userNumber
	 * @param passworld
	 * @return
	 */
	public int teacherLogin(String userNumber, String passworld) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call teacherLogin(?,?,?) }");
			statement.setString(1, userNumber);
			statement.setString(2, passworld);
			statement.registerOutParameter(3, java.sql.Types.INTEGER);
			statement.execute();
			return statement.getInt(3);
		} catch (SQLException e) {
			e.printStackTrace();
			return 40;
		}
	}
	
	/**
	 * 老师查看签到情况
	 * @return 返回签到情况set
	 */
	public List<ArrayList<String>> searchClassSigned(String className) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call searchClassSigned(?) }");
			statement.setString(1, className);
			ResultSet set = statement.executeQuery();
			List<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
			while (set.next()) {
				ArrayList<String> tempList = new ArrayList<String>();
				String signedClass = set.getString(1);
				String signedName = set.getString(2);
				String signedTime = set.getString(3);
				tempList.add(signedClass);
				tempList.add(signedName);
				tempList.add(signedTime);
				dataList.add(tempList);
				System.out.println("签到者信息如下：\r\n编号：" + signedClass + " \r\n姓名："
						+ signedName + "\r\n签到时间" + signedTime);
			}
			return dataList;
		} catch (SQLException e) {
			System.out.println("没有相关数据");
			return null;
		}
	}
	
	/**
	 * 老师查看答题情况
	 * @return 返回签到情况set
	 */
	public List<ArrayList<String>> searchAnswer(String className,String paperName) {
		try {
			CallableStatement statement = connection
					.prepareCall("{ call searchAnswer(?,?) }");
			statement.setString(1, className);
			statement.setString(2, paperName);
			ResultSet set = statement.executeQuery();
			List<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
			while (set.next()) {
				ArrayList<String> tempList = new ArrayList<String>();
				String exercisesid = set.getString(1);
				String userName = set.getString(2);
				String result = set.getString(3);
				tempList.add(exercisesid);
				tempList.add(userName);
				tempList.add(result);
				dataList.add(tempList);
				System.out.println("答题信息如下：\r\n编号：" + exercisesid + " \r\n姓名："
						+ userName + "\r\n签到时间" + result);
			}
			return dataList;
		} catch (SQLException e) {
			System.out.println("没有相关数据");
			return null;
		}
	}
	
//	public static void main(String[] args) {
//		try {
//			new Java_JDBC().searchAnswer("A110", "大学化学");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//	}
	
}
